<html>
    <title>Purchase Reports</title>
    <body>

        <%@ page errorPage="error.jsp" %>
        <%@ page import="net.sf.jasperreports.engine.*" %>
        <%@ page import="com.myapp.struts.reports.*" %>
        <%@ page import="com.myapp.struts.dao.*" %>
        <%@ page import="net.sf.jasperreports.engine.util.*" %>
        <%@ page import="net.sf.jasperreports.engine.export.*" %>
        <%@ page import="net.sf.jasperreports.j2ee.servlets.*" %>
        <%@ page import="net.sf.jasperreports.engine.util.JRProperties" %>
        <%@ page import="java.util.*" %>
        <%@ page import="java.io.*" %>
        <%@ page import="java.sql.* , net.sf.jasperreports.engine.base.*,com.myapp.struts.dao.*,java.math.*"%>
        <%
        Connect conn = new Connect();
        request.getSession(true);
        String r = session.getAttribute("rd").toString();
        String v = session.getAttribute("vd").toString();
        String supp = session.getAttribute("supp").toString();
        System.out.println("R" + r);
        System.out.println("v" + v);
        System.out.println("supp" + supp);

        Vector v1 = new Vector();
        Vector v2 = new Vector();
        Vector v3 = new Vector();
        Vector v4 = new Vector();
        Vector v5 = new Vector();
        Vector v6 = new Vector();
        Vector v7 = new Vector();
        ResultSet res = null, r1 = null, r2 = null, r3 = null;
        PreparedStatement pstmt;
        ResultSet rs;
        int data = 0;
        String loc = "";
        Connection con = null;
        con = conn.mySql();
        if (con != null) {
            File reportFile = new File(application.getRealPath("/jrxml/PurchaseReportSupplierWise.jasper"));

            if (!reportFile.exists()) {
                throw new JRRuntimeException("File PurchaseReportSupplierWise.jrxml not found. The report design must be compiled first.");
            }
            JasperReport jasperReport = (JasperReport) JRLoader.loadObject(reportFile.getPath());
            Map parameters = new HashMap();
            parameters.put("ReportTitle", "Address Report");
            parameters.put("BaseDir", reportFile.getParentFile());

            String sqa = "select count(*) from purchase where sname='" + supp + "' and  pdate >= '" + r + "' and pdate <= '" + v + "'";
            System.out.println(sqa);
            pstmt = con.prepareStatement(sqa);
            rs = pstmt.executeQuery();

            if (rs.next() == true) {
                data = (int) rs.getInt(1);
            }
            pstmt.close();
            rs.close();
            //---------------------------------------------
            String sql = "SELECT purchase.iname,purchase.sname,purchase.qty,purchase.rate,purchase.value,purchase.pdate,items.uom FROM purchase,items  where items.itemname=purchase.iname and sname='" + supp + "' and pdate >= '" + r + "' and pdate<='" + v + "' order by sname";
            System.out.println(sql);
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();

            String ena = "", client = "", location = "";
            double qty = 0;
            java.util.Date ncdate = null;
            double rate = 0.0, value1 = 0.0;
            while (rs.next() == true) {
                ena = (String) rs.getString(1);

                client = (String) rs.getString(2);

                qty = (double) rs.getInt(3);

                rate = (double) rs.getDouble(4);

                value1 = (double) rs.getDouble(5);

                ncdate = (java.util.Date) rs.getDate(6);

                v1.add(ena);
                v2.add(client);
                v3.add(qty);
                v4.add(rate);
                v5.add(value1);
                v6.add(ncdate);
                v7.add(rs.getString(7));


            }
            //---------------------------------------------
            JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, parameters, new PurchaseRepSUPWise(data, v1, v2, v3, v4, v5, v6, v7));

            File destFile = new File(reportFile.getParent(), jasperPrint.getName() + ".xls");
            OutputStream output = new FileOutputStream(destFile);

            JExcelApiExporter exporterXLS = new JExcelApiExporter();

            exporterXLS.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint);
            exporterXLS.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);
            exporterXLS.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);
            exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
            exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, output);
            response.setHeader("Content-Disposition", "inline;filename=" + destFile);
            response.setContentType("application/vnd.ms-excel");
            exporterXLS.exportReport();
            con.close();

        }
        %>

        <div align="center">
            <p>&nbsp;</p>
            <p>&nbsp;</p>
            <p>&nbsp;</p>
            <p>&nbsp;</p>
            <h2 align="center" class="style1">
                <jsp:forward page="../../../../jrxml/PurchaseReportSupplierWise.xls"/>
            </h2>
        </div>
    </body>
</html>